create or replace
PACKAGE BODY PA_SPECTACLE IS

	current_spectacle spectacle%ROWTYPE;

	FUNCTION GetAll return sqlcur IS
	  c1 sqlcur;
		BEGIN
			OPEN c1 FOR
				SELECT
						*
				FROM
					view_spectacle
				ORDER BY 
					num_spectacle;
			RETURN c1;
	END GetAll;
	
	FUNCTION GetAllHome return sqlcur IS
	  c1 sqlcur;
		BEGIN
			OPEN c1 FOR
				SELECT
						*
				FROM
					view_spectacle
				WHERE
					etat_spectacle = 1
				ORDER BY 
					num_spectacle;
			RETURN c1;
	END GetAllHome;

	FUNCTION GetSpectacles
	(
		  p_num_typespect NUMBER default null
		, p_nom_spectacle VARCHAR2 default null
		, p_ville_salle VARCHAR2 default null
                , p_date_debut VARCHAR2 default null
                , p_date_fin VARCHAR2 default null
	)
	return sqlcur IS
	  cur sqlcur;
	  isFirst boolean := true;
	  req VARCHAR2(500);
	BEGIN
      req := 'select distinct spec.num_spectacle, spec.nom_spectacle, spec.date_debut_spectacle, spec.date_fin_spectacle, typ.lib_typespect from ' ||
		'spectacle spec inner join type_spectacle typ on typ.num_typespect = spec.num_typespect ' ||
                'inner join representation rep on rep.num_spectacle = spec.num_spectacle';

      if p_ville_salle is not null then
        req := req || ' inner join salle sal on sal.num_salle = rep.num_salle where ';
        execute immediate 'alter session set nls_sort=Latin_AI';
        execute immediate 'alter session set nls_comp=linguistic';
        req := req || 'regexp_like(sal.ville_salle, :v)';
        isFirst := false;
      end if;

      if p_num_typespect is not null then
        if isFirst = true then
          req := req || ' where ';
        else
          req := req || ' and ';
        end if;
        req := req || 'spec.num_typespect = ' || p_num_typespect;
        isFirst := false;
      end if;

      if p_nom_spectacle is not null then
        if isFirst = true then
          req := req || ' where ';
        else
          req := req || ' and ';
        end if;
        execute immediate 'alter session set nls_sort=Latin_AI';
        execute immediate 'alter session set nls_comp=linguistic';
        req := req || 'regexp_like(spec.nom_spectacle, :b)';
        isFirst := false;
      end if;
      
      if p_date_debut is not null then
        if isFirst = true then
          req := req || ' where ';
        else
          req := req || ' and ';
        end if;
        req := req || 'rep.date_debut >= ''' || to_date(p_date_debut, 'DD/MM/YYYY') || '''';
        isFirst := false;
      end if;
      
      if p_date_fin is not null then
        if isFirst = true then
          req := req || ' where ';
        else
          req := req || ' and ';
        end if;
        req := req || 'rep.date_debut <= ''' || to_date(p_date_fin, 'DD/MM/YYYY') || '''';
        isFirst := false;
      end if;

      req := req || ' order by spec.nom_spectacle';
      
      dbms_output.put_line(req);

      if p_ville_salle is not null and p_nom_spectacle is not null then
        OPEN cur FOR req using p_ville_salle, p_nom_spectacle;
      elsif p_ville_salle is null and p_nom_spectacle is not null then
        OPEN cur FOR req using p_nom_spectacle;
      elsif p_ville_salle is not null and p_nom_spectacle is null then
        OPEN cur FOR req using p_ville_salle;
      else
        OPEN cur FOR req;
      end if;

      return cur;

  END;

  FUNCTION Get(p_num_spectacle in number) return spectacle%ROWTYPE IS
		CURSOR c_spectacle(numspectacle in number)
		IS
			SELECT
				*
			FROM
				SPECTACLE SPEC
			WHERE
				NUM_SPECTACLE = numspectacle;
	BEGIN
		OPEN c_spectacle(p_num_spectacle);
		FETCH c_spectacle INTO current_spectacle;

		IF c_spectacle%NOTFOUND THEN
			RAISE NO_DATA_FOUND;
		END IF;

		CLOSE c_spectacle;
		RETURN current_spectacle;
  END;

  FUNCTION GetView(p_num_spectacle in number) return view_spectacle%ROWTYPE AS
		view_spect view_spectacle%rowtype;
                CURSOR c_spectacle(numspectacle in number)
		IS
			SELECT
				*
			FROM
				VIEW_SPECTACLE SPEC
			WHERE
				NUM_SPECTACLE = numspectacle;
	BEGIN
		OPEN c_spectacle(p_num_spectacle);
		FETCH c_spectacle INTO view_spect;

		IF c_spectacle%NOTFOUND THEN
			RAISE NO_DATA_FOUND;
		END IF;

		CLOSE c_spectacle;
		RETURN view_spect;
  END;

  FUNCTION GetAuteurs(p_num_spectacle in number) return sqlcur is
               c1 sqlcur;
    BEGIN
        OPEN c1 FOR
            SELECT
                  ecr.num_auteur
            FROM
                  ecrire ecr
            WHERE
                  num_spectacle = p_num_spectacle;
        RETURN c1;
  END;

    FUNCTION GetMetteurs(p_num_spectacle in number) return sqlcur is
               c1 sqlcur;
    BEGIN
        OPEN c1 FOR
            SELECT
                  met.num_met_scene
            FROM
                  mettre_en_scene met
            WHERE
                  num_spectacle = p_num_spectacle;
        RETURN c1;
  END;

    FUNCTION GetArtistes(p_num_spectacle in number) return sqlcur is
               c1 sqlcur;
    BEGIN
        OPEN c1 FOR
            SELECT
                    jou.num_artiste
                  , jou.ordre_jouer
            FROM
                  jouer jou
            WHERE
                  num_spectacle = p_num_spectacle;
        RETURN c1;
  END;

	PROCEDURE Add
	(
		  p_num_typespect number
		, p_nom_spectacle VARCHAR2
		, p_descr_spectacle in CLOB
		, p_duree_spectacle number
		, p_date_debut_spectacle date
		, p_date_fin_spectacle date default null
		, p_nums_auteur tab default null
		, p_nums_metteur_scene tab default null
		, p_artistes tabArtiste default null
	)
	AS
	p_num_spectacle number;
	BEGIN
		INSERT INTO SPECTACLE (NUM_SPECTACLE, NUM_TYPESPECT, NOM_SPECTACLE, DESCR_SPECTACLE, DUREE_SPECTACLE, DATE_DEBUT_SPECTACLE, DATE_FIN_SPECTACLE)
		VALUES (seq_spectacle.nextval, p_num_typespect, p_nom_spectacle, p_descr_spectacle, p_duree_spectacle, p_date_debut_spectacle, p_date_fin_spectacle);

		if p_nums_auteur.count > 0 then
			FOR i IN p_nums_auteur.FIRST .. p_nums_auteur.LAST
			LOOP
				insert into ecrire(num_spectacle, num_auteur)
				values (seq_spectacle.currval, p_nums_auteur(i));
			END LOOP;
		end if;

		if p_nums_metteur_scene.count > 0 then
		  FOR i IN p_nums_metteur_scene.FIRST .. p_nums_metteur_scene.LAST
		  LOOP
			 insert into mettre_en_scene(num_spectacle, num_met_scene)
			 values (seq_spectacle.currval, p_nums_metteur_scene(i));
		  END LOOP;
		end if;

		if p_artistes.count > 0 then
		  FOR i IN p_artistes.FIRST .. p_artistes.LAST
		  LOOP
			  insert into jouer(num_spectacle, num_artiste, ordre_jouer)
			  values (seq_spectacle.currval, p_artistes(i).num_artiste, p_artistes(i).ordre_jouer);
		  END LOOP;
		end if;

		COMMIT;
	END;

  PROCEDURE Upd(
                  p_num_spectacle NUMBER
                , p_num_typespect number
                , p_nom_spectacle VARCHAR2
                , p_descr_spectacle in CLOB
                , p_duree_spectacle number
                , p_date_debut_spectacle date
                , p_date_fin_spectacle date  default null
                , p_nums_auteur tab
                , p_nums_metteur_scene tab
                , p_artistes tabArtiste default null
                )
 AS
	BEGIN
                UPDATE SPECTACLE
		set
                      NUM_TYPESPECT = p_num_typespect
                    , NOM_SPECTACLE = p_nom_spectacle
                    , DESCR_SPECTACLE = p_descr_spectacle
                    , DUREE_SPECTACLE = p_duree_spectacle
                    , DATE_DEBUT_SPECTACLE = p_date_debut_spectacle
                    , DATE_FIN_SPECTACLE = p_date_fin_spectacle
                where
                    num_spectacle = p_num_spectacle;

                -- suppression des auteurs, artistes et metteurs en scene
                delete from ecrire
                where num_spectacle = p_num_spectacle;

                delete from mettre_en_scene
                where num_spectacle = p_num_spectacle;

                delete from jouer
                where num_spectacle = p_num_spectacle;

                if p_nums_auteur.count > 0 then
                  FOR i IN p_nums_auteur.FIRST .. p_nums_auteur.LAST
                  LOOP
                      insert into ecrire(num_spectacle, num_auteur)
                      values (p_num_spectacle, p_nums_auteur(i));
                  END LOOP;
                end if;

                if p_nums_metteur_scene.count > 0 then
                  FOR i IN p_nums_metteur_scene.FIRST .. p_nums_metteur_scene.LAST
                  LOOP
                      insert into mettre_en_scene(num_spectacle, num_met_scene)
                      values (p_num_spectacle, p_nums_metteur_scene(i));
                  END LOOP;
                end if;

                if p_artistes.count > 0 then
                  FOR i IN p_artistes.FIRST .. p_artistes.LAST
                  LOOP
                      insert into jouer(num_spectacle, num_artiste, ordre_jouer)
                      values (p_num_spectacle, p_artistes(i).num_artiste, p_artistes(i).ordre_jouer);
                  END LOOP;
                end if;
    	COMMIT;
	END;

	PROCEDURE Del(p_num_spectacle NUMBER) AS
	BEGIN
		delete from spectacle
		where num_spectacle = p_num_spectacle;
	END Del;

	PROCEDURE UpdEtat
	(
		  p_num_spectacle in NUMBER
		, p_etat in NUMBER
	)
	AS
	BEGIN
		update
			spectacle
		set
			etat_spectacle = p_etat
		where
			num_spectacle = p_num_spectacle;
		commit;
	END;

END PA_SPECTACLE;
/